INF: TBU Throughput Limitation for MSDE, Desktop, SBS, Office Ve[sqlserver]
ID: Q231406    CREATED: 06-MAY-1999   MODIFIED: 15-NOV-1999
winnt:7.0
winnt
MSONLY | 

\* Security       : MSONLY
======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft SQL Server version 7.0 
-------------------------------------------------------------------------------

SUMMARY
=======

To provide a commensurate relationship of performance to product revenue, SQL
Server 7.0 uses an artificial throughput limitation called Target Benchmark
Users (TBU) for the non-server versions of SQL Server. This includes Microsoft
Data Engine (MSDE), Desktop, Small Business Server, and Office versions.
Standard and Enterprise have no artificial performance limits.

MORE INFORMATION
================

TBU is different from the previous SQL 6.5 Concurrent Working Limit (CWL)
implementation. SQL 6.5 CWL was essentially thread-based. It was an Open Data
Services (ODS) CWL implementation. SQL 6.5 CWL was essentially thread-based. It
was an ODS counting semaphore that limited the number of outstanding requests,
essentially limiting the number of concurrently running threads. Under certain
conditions it could cause blocking, hanging or other artifacts. For more
information, please see bug 35636 (SQLBUG_70) "DCR: Allow local connection
beyond CWL on restricted Desktop versions of SQL Server".

SQL 7.0 TBU is delay-based and should provide an artifact-free performance
ceiling based on concurrent activity. If the concurrent threads are greater than
TBU, a variable delay is induced within bufget(). The amount of delay in mS is
done according to the following formula:

   delay in mS = concurrent workers - (6 + TBU limit)

where concurrent workers are the number of currently active threads or fibers.
The TBU limit is 30 for SBS, 8 for MSDE, Office and Desktop, and 6 is the fudge
factor to compensate for system threads. In SQL 7.0 the system threads are lazy
write, log writer, lock monitor, checkpoint, cleanup, and signal. Please see
RESOURCE::DelayExecution. Also search on GovDelayTimes, and rCWL.

This imposes a progressive throttle effect in proportion to how far over the TBU
limit we are. If concurrent workers drops below TBU, the delay is reduced 1 mS
per call to bufget() until it reaches zero. This evidently adds a hysteresis
effect to avoid TBU cutting on/off suddenly.

Essentially, there is no performance penalty for checking TBU, since a single IF
test in bufget() does this. Only if TBU is enabled is further code executed to
calculate the amount of delay and other related items.

This scheme provides a more natural, less intrusive throttle effect than CWL.
There's no limit imposed on user connections. Since bufget() may fetch physical
pages (if cache miss), placing the delay there avoids problems with delaying
"hot" code paths; a possible delay is expected anyway due to page fetch. TBU
only increases this amount. Note in the SQL 7.0 source code that various CWL
variables and identifiers still exist and are used by TBU.

In a performance problem on TBU versions, how do you tell if the slowdown is
caused by TBU or by natural factors? Since TBU delays only engage when exceeding
the threshold, the simplest way is examine how many concurrent users there are.
Query sysprocesses for this. For example, SELECT COUNT(*) FROM SYSPROCESSES
WHERE STATUS <> 'sleeping'.

If less than TBU threshold + 6, a performance problem is likely of natural
causes. If greater than this, the artificial delays are a factor. In that case
the customer should upgrade to a higher version, or reduce the amount of
concurrent activity.

Since the natural course of resolving most performance problems involve isolating
problem queries and running them in isolation, this automatically eliminates TBU
effects. In most cases it shouldn't be necessary to use specialized techniques
beyond this.

However if necessary an undocumented DBCC allows inspection of TBU limits. The
following was taken from SQL development e-mail:

   DBCC USAGEGOVERNOR
   Display and set parameters related to usage governor

For external use (in the shipping server):

 
   dbcc usagegovernor(0,0): alloc, set the usage violation counters to all 0.
   dbcc usagegovernor(0,1): display the current violation counters.
   dbcc usagegovernor(0,2): dealloc the usage violation counters.

How to use:

Start the server in a console window. The output of this command is to the trace.
You can use dbcc traceon (3604) to send the trace to the client.

   dbcc traceon(3604)
   go

DBCC execution completed. If DBCC printed error messages, contact your system
administrator.

   dbcc usagegovernor (0,0)
   go

Allocate usage violation counters Set usage violation counters to 0. DBCC
execution completed. If DBCC printed error messages, contact your system
administrator.

Now the counter has started counting. You can run any query you like. To display
the counter values:

   dbcc usagegovernor (0,1)
   go

TBU = 8
Usage Violation         Counter Value
        1               0
        2               0
        3               0
        4               0
        5               0
        6               0
??
        126             0
        127             0
        128             0

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If counter values are all 0, you don?t have any usage violation. A non-zero value
means how many times you have concurrent worker threads exceeding TBU. The
violation number means how many more concurrent threads you have exceeding TBU.
For example, if you see:

TBU = 8
Usage Violation         Counter Value
        1               100
        2               200

That means you have 9 concurrent worker threads for 100 times, and 10 concurrent
worker threads for 200 times. For each of the violation the thread is delayed by
a certain amount.

You can use dbcc usagegovernor(0,0) to reset the usage violation counters to all
0, and dbcc usagegovernor(0,1) to display the current violation counters any
time you want.

Finally, you use dbcc usagegovernor(0,2) to deallocate the usage violation
counters:

   dbcc usagegovernor(0,2)
   go

Deallocate usage violation counters. DBCC execution completed. If DBCC printed
error messages, contact your system administrator.

For more information, please see bug 50367 (SQLBUG_70) "dbcc usagegovernor trace
output is garbled, also, need to display TBU values in retail version".

Additional query words:

\* MSONLY Document Information
\* ===========================
\*
======================================================================
Keywords          :  
Version           : winnt:7.0
Platform          : winnt
Issue type        : kbinfo
\* Edit Status    : Published
\* Tech Status    : Approved
================================================================================
Created_by: joema      Edit_review: vernettp  Edited: 16-JUN-1999
Modified_by: stephd    Tech_review: irad      Reviewed: 15-JUN-1999
